package com.wf.ew.orderB.biz.impl;

import com.wf.ew.common.BaseDao;
import com.wf.ew.common.PageResult;
import com.wf.ew.common.constant.UserConstant;
import com.wf.ew.common.utils.Utils;
import com.wf.ew.orderB.biz.BillBiz;
import com.wf.ew.orderB.model.Bill;
import com.wf.ew.system.model.User;
import net.sf.json.JSONObject;
import org.apache.poi.ss.formula.functions.T;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 *
 * 数据层实现类
 * @author : Doman
 * @date : 2020-04-13 13:50:45
 * @version ：1.0
 */
@Repository
public class BillBizImpl extends BaseDao implements BillBiz {

	@Override
	public String allApiList(PageResult<T> page, Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
		sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
		sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
		sql.append(",cmy.name companyName,suser.name createName ");
		sql.append(" from sszg_bill b ");
		sql.append(" inner join sys_user suser on suser.id=b.createUser ");
		sql.append(" inner join we_company cmy on cmy.id=b.companyId ");
		sql.append(" where b.del=0 and cmy.del=0 ");
		if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getPackDate())){
				String[] dates = bill.getPackDate().split("~");
				sql.append(" and b.packDate > :packDate1 ");
				map.put("packDate1", dates[0].trim());
				sql.append(" and b.packDate < :packDate2 ");
				map.put("packDate2", dates[1].trim());
			}
			if(Utils.isNotEmpty(bill.getBoxNum())){
				sql.append(" and instr(b.boxNum,:boxNum)>0 ");
				map.put("boxNum", bill.getBoxNum());
			}
			if(Utils.isNotEmpty(bill.getBillOfLading())){
				sql.append(" and b.billOfLading=:billOfLading ");
				map.put("billOfLading", bill.getBillOfLading());
			}
			if(Utils.isNotEmpty(bill.getBoxType())){
				sql.append(" and b.boxType=:boxType ");
				map.put("boxType", bill.getBoxType());
			}
		}
		if(Utils.isNotEmpty(bill.getSort())&&Utils.isNotEmpty(bill.getOrder())){
			sql.append(" order by "+bill.getSort()+" "+bill.getOrder());
		} else {
			sql.append(" order by b.packDate desc ");
		}
		return jsonPage(getPageQueryList(page, sql.toString(), map, rm));
	}

	@Override
	public List<Bill> allApiList(Bill bill) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
		sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
		sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
		sql.append(",cmy.name companyName,suser.name createName ");
		sql.append(" from sszg_bill b ");
		sql.append(" inner join sys_user suser on suser.id=b.createUser ");
		sql.append(" inner join we_company cmy on cmy.id=b.companyId ");
		sql.append(" where b.del=0 and cmy.del=0 ");
		if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getBillOfLading())){
				sql.append(" and b.billOfLading=:billOfLading ");
				map.put("billOfLading", bill.getBillOfLading());
			}
			if(Utils.isNotEmpty(bill.getBoxType())){
				sql.append(" and b.boxType=:boxType ");
				map.put("boxType", bill.getBoxType());
			}
			if(Utils.isNotEmpty(bill.getCustomer())){
				sql.append(" and b.customer=:customer ");
				map.put("customer", bill.getCustomer());
			}
			if(Utils.isNotEmpty(bill.getBusiId())){
				sql.append(" and b.busiId=:busiId ");
				map.put("busiId", bill.getBusiId());
			}
		}
		if(Utils.isNotEmpty(bill.getSort())&&Utils.isNotEmpty(bill.getOrder())){
			sql.append(" order by "+bill.getSort()+" "+bill.getOrder());
		} else {
			sql.append(" order by b.packDate desc ");
		}
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}


	@Override
	public List<Bill> allApiListByNow(Bill bill) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
		sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
		sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
		sql.append(",cmy.name companyName,suser.name createName ");
		sql.append(" from sszg_bill b ");
		sql.append(" inner join sys_user suser on suser.id=b.createUser ");
		sql.append(" inner join we_company cmy on cmy.id=b.companyId ");
		sql.append(" where b.del=0 and cmy.del=0 ");
		if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getBillOfLading())){
				sql.append(" and b.billOfLading=:billOfLading ");
				map.put("billOfLading", bill.getBillOfLading());
			}
			if(Utils.isNotEmpty(bill.getBoxType())){
				sql.append(" and b.boxType=:boxType ");
				map.put("boxType", bill.getBoxType());
			}
			if(Utils.isNotEmpty(bill.getCustomer())){
				sql.append(" and b.customer=:customer ");
				map.put("customer", bill.getCustomer());
			}
		}
		sql.append(" and  DATE_FORMAT(now(),'%Y-%m-%d 00:00:00') <=b.packDate and b.packDate <= DATE_FORMAT(now(),'%Y-%m-%d 20:00:00') ");
		if(Utils.isNotEmpty(bill.getSort())&&Utils.isNotEmpty(bill.getOrder())){
			sql.append(" order by "+bill.getSort()+" "+bill.getOrder());
		} else {
			sql.append(" order by b.packDate desc ");
		}
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public List<Bill> selectShareOrders(Bill bill) {
		Map<String, Object> map  = new HashMap<String, Object>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
		sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
		sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
		sql.append(",cmy.name companyName,suser.name createName ");
		sql.append(" from sszg_bill b ");
		sql.append(" inner join sys_user suser on suser.id=b.createUser ");
		sql.append(" inner join we_company cmy on cmy.id=b.companyId ");
		sql.append(" where b.del=0 and cmy.del=0 ");
		sql.append(" and b.id in ( :ids ) order by b.packDate desc ");
//		String ids = "";
//		String[] idsArr = bill.getIds().split(",");
//		if(idsArr != null && idsArr.length > 0) {
//			for(int i = 0; i < idsArr.length; i ++) {
//				if(i != 0) {
//					ids += "'";
//				}
//				ids += idsArr[i];
//				if(i != idsArr.length -1) {
//					ids += "',";
//				}
//			}
//		}
//		if (ids.equals("")) {
//			return null;
//		}
		System.out.println("==========="+bill.getIds());
		map.put("ids", Arrays.asList(bill.getIds().split(",")));
		getMapParameterObj(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}


	@Override
	public String list(PageResult<T> page, Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
		sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
		sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
        sql.append(",cmy.name companyName,suser.name createName ");
        sql.append(" from sszg_bill b ");
        sql.append(" inner join sys_user suser on suser.id=b.createUser ");
        sql.append(" inner join we_company cmy on cmy.id=b.companyId ");
        sql.append(" where b.del=0 and cmy.del=0 ");

		if(loginUser.getRoleState().equals("3")) {
			if(Utils.isNotEmpty(bill)){
				if(Utils.isNotEmpty(bill.getBillOfLading())){
					JSONObject data = new JSONObject();
					data.put("count", 0);
					data.put("data", null);
					data.put("msg", "查询成功");
					data.put("code", 0);
					return data.toString();
				}
			}
			sql.append(" and b.companyId=:tablecompanyId ");
			map.put("tablecompanyId", loginUser.getCompanyId());
			sql.append(" and b.customer=:customer ");
			map.put("customer", loginUser.getCustomerName());
			sql.append(" and b.billOfLading=:billOfLading ");
			map.put("billOfLading", bill.getBillOfLading());
		} else if(!loginUser.getId().equals(UserConstant.ADMIN_ID)){
			if(loginUser.getRoleState().equals("2")) {
				sql.append(" and b.companyId=:tablecompanyId ");
				map.put("tablecompanyId", loginUser.getCompanyId());
				sql.append(" and b.createUser=:createUser ");
				map.put("createUser", loginUser.getId());
			}
			if(Utils.isNotEmpty(bill)){
				if(Utils.isNotEmpty(bill.getPackDate())){
					String[] dates = bill.getPackDate().split("~");
					sql.append(" and b.packDate > :packDate1 ");
					map.put("packDate1", dates[0].trim());
					sql.append(" and b.packDate < :packDate2 ");
					map.put("packDate2", dates[1].trim());
				}
				if(Utils.isNotEmpty(bill.getBoxNum())){
					sql.append(" and instr(b.boxNum,:boxNum)>0 ");
					map.put("boxNum", bill.getBoxNum());
				}
				if(Utils.isNotEmpty(bill.getBillOfLading())){
					sql.append(" and instr(b.billOfLading,:billOfLading)>0 ");
					map.put("billOfLading", bill.getBillOfLading());
				}
			}
        }
		if(Utils.isNotEmpty(bill.getSort())&&Utils.isNotEmpty(bill.getOrder())){
			sql.append(" order by "+bill.getSort()+" "+bill.getOrder());
		} else {
			sql.append(" order by b.packDate desc ");
		}

		return jsonPage(getPageQueryList(page, sql.toString(), map, rm));
	}

	@Override
	public List<Bill> list(Bill bill, User loginUser) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
        StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
		sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
		sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
        sql.append(",cmy.name companyName,suser.name createName ");
        sql.append(" from sszg_bill b ");
        sql.append(" inner join sys_user suser on suser.id=b.createUser ");
        sql.append(" inner join we_company cmy on cmy.id=b.companyId ");
        sql.append(" where b.del=0 and cmy.del=0 ");
		if(loginUser.getRoleState().equals("3")) {
			if(Utils.isNotEmpty(bill)){
				if(Utils.isNotEmpty(bill.getBillOfLading())){
					return null;
				}
			}
			sql.append(" and b.companyId=:tablecompanyId ");
			map.put("tablecompanyId", loginUser.getCompanyId());
			sql.append(" and b.customer=:customer ");
			map.put("customer", loginUser.getCustomerName());
			sql.append(" and b.billOfLading=:billOfLading ");
			map.put("billOfLading", bill.getBillOfLading());
		} else if(!loginUser.getId().equals(UserConstant.ADMIN_ID)){
			if(loginUser.getRoleState().equals("2")) {
				sql.append(" and b.companyId=:tablecompanyId ");
				map.put("tablecompanyId", loginUser.getCompanyId());
				sql.append(" and b.createUser=:createUser ");
				map.put("createUser", loginUser.getId());
			}
			if(Utils.isNotEmpty(bill)){
				if(Utils.isNotEmpty(bill.getPackDate())){
					String[] dates = bill.getPackDate().split("~");
					sql.append(" and b.packDate > :packDate1 ");
					map.put("packDate1", dates[0].trim());
					sql.append(" and b.packDate < :packDate2 ");
					map.put("packDate2", dates[1].trim());
				}
				if(Utils.isNotEmpty(bill.getBoxNum())){
					sql.append(" and instr(b.boxNum,:boxNum)>0 ");
					map.put("boxNum", bill.getBoxNum());
				}
				if(Utils.isNotEmpty(bill.getBillOfLading())){
					sql.append(" and instr(b.billOfLading,:billOfLading)>0 ");
					map.put("billOfLading", bill.getBillOfLading());
				}
			}
		}
		if(Utils.isNotEmpty(bill.getSort())&&Utils.isNotEmpty(bill.getOrder())){
			sql.append(" order by "+bill.getSort()+" "+bill.getOrder());
		} else {
			sql.append(" order by b.packDate desc ");
		}

		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public List<Bill> selectWkOrderFirst(Bill bill) {
		Map<String, String> map  = new HashMap<String, String>();
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
        StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
		sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
		sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
        sql.append(",ifnull((select u.registerPhone from sys_user u where u.id=b.createUser),'') as tsphone");
        sql.append(",(select ss.score from sszg_score ss where b.id=ss.billId) score ");
        sql.append(" from sszg_bill b ");
        sql.append(" where b.del=0 and b.carNo=:carNo ");
		map.put("carNo", bill.getCarNo());
    	if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getKeywords())){
				sql.append(" and (select count(1) from sszg_score ssr where ssr.billId=b.id)=0 ");
			}
			if(Utils.isNotEmpty(bill.getContactsPhone())){
				sql.append(" and b.contactsPhone=:contactsPhone ");
				map.put("contactsPhone", bill.getContactsPhone());
			}
		}
		sql.append(" and DATE_FORMAT(now(),'%Y-%m-%d 00:00:00') <=b.packDate and b.packDate <= DATE_FORMAT(now(),'%Y-%m-%d 20:00:00') ");
		sql.append(" order by b.packDate desc LIMIT 1");
		getMapParameter(sql, map);
		return jdbcTemplate.query(sql.toString(),map, rm);
	}

	@Override
	public int selectCountOrderDay(Bill bill) {
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select count(1) from sszg_bill b where b.del=0 ");
		if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getCarNo())){
				sql.append(" and b.carNo=:carNo ");
				map.put("carNo", bill.getCarNo());
			}
			if(Utils.isNotEmpty(bill.getContactsPhone())){
				sql.append(" and b.contactsPhone=:contactsPhone ");
				map.put("contactsPhone", bill.getContactsPhone());
			}
		}
		sql.append(" and  DATE_FORMAT(now(),'%Y-%m-%d 00:00:00') <=b.packDate and b.packDate <= DATE_FORMAT(now(),'%Y-%m-%d 20:00:00') ");
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(),map, Integer.class);
	}

	@Override
	public int selectCountOfScore(Bill bill) {
		Map<String, String> map  = new HashMap<String, String>();
        StringBuffer sql = new StringBuffer("select count(1) from sszg_bill b, sszg_score s where b.id=s.billId and b.del=0 ");
    	if(Utils.isNotEmpty(bill)){
			if(Utils.isNotEmpty(bill.getCarNo())){
				sql.append(" and b.carNo=:carNo ");
				map.put("carNo", bill.getCarNo());
			}
			if(Utils.isNotEmpty(bill.getContactsPhone())){
				sql.append(" and b.contactsPhone=:contactsPhone ");
				map.put("contactsPhone", bill.getContactsPhone());
			}
		}
		sql.append(" and DATE_FORMAT(now(),'%Y-%m-%d 00:00:00') <=b.packDate and b.packDate <= DATE_FORMAT(now(),'%Y-%m-%d 20:00:00') ");

		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(),map, Integer.class);
	}

	@Override
	public Bill getById(Bill bill) {
		RowMapper<Bill> rm = new BeanPropertyRowMapper<Bill>(Bill.class);
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql = new StringBuffer("select b.id,b.companyId,b.relUserId");
        sql.append(",FORMAT((select avg(s.score) from sszg_score s where s.carNo=b.carNo and s.del=0),0) score");
        sql.append(",b.qrcode,b.packDate,b.customer");
		sql.append(",b.factoryAddress,b.contacts,b.contactsPhone,b.billOfLading");
		sql.append(",b.boxType,b.boxNum,b.driver,b.carNo");
		sql.append(",b.driverPhone,b.recordPeople,b.getBoxLastDate,b.privatecode");
		sql.append(",b.importDate,b.sealno,b.vessel,b.serialno");
		sql.append(",b.voyage,b.weight,b.orderNum,b.remark");
		sql.append(",b.busiId,b.getConPile,b.returnConPile");
		sql.append(",b.createTime,b.createUser,b.updateTime,b.updateUser");
        sql.append(",cmy.name companyName,suser.name createName ");
        sql.append(" from sszg_bill b ");
        sql.append(" inner join sys_user suser on suser.id=b.createUser ");
        sql.append(" inner join we_company cmy on cmy.id=b.companyId ");
        sql.append(" where b.del=0 and cmy.del=0 and b.id=:id ");
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		return jdbcTemplate.queryForObject(sql.toString(), map, rm);
	}

	@Override
	public boolean add(Bill bill, User loginUser) {
		int count = 1;
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("insert into sszg_bill (");
        sql.append("companyId,relUserId,score,qrcode");
		sql.append(",packDate,customer,factoryAddress,contacts");
		sql.append(",contactsPhone,billOfLading,boxType,boxNum");
		sql.append(",driver,carNo,driverPhone,recordPeople");
		sql.append(",getBoxLastDate,privatecode,importDate,sealno");
		sql.append(",vessel,serialno,voyage,weight");
		sql.append(",busiId,getConPile,returnConPile");
		sql.append(",orderNum,remark,createUser,jkxid) values ( ");
        sql.append(":companyId,:relUserId,:score,:qrcode");
		sql.append(",:packDate,:customer,:factoryAddress,:contacts");
		sql.append(",:contactsPhone,:billOfLading,:boxType,:boxNum");
		sql.append(",:driver,:carNo,:driverPhone,:recordPeople");
		sql.append(",:getBoxLastDate,:privatecode,:importDate,:sealno");
		sql.append(",:vessel,:serialno,:voyage,:weight");
		sql.append(",:busiId,:getConPile,:returnConPile");
		sql.append(",:orderNum,:remark,:createUser,:jkxid)");
        map.put("companyId", loginUser.getCompanyId());
        map.put("relUserId", bill.getRelUserId());
        map.put("score", bill.getScore());
        map.put("qrcode", bill.getQrcode());
        map.put("packDate", bill.getPackDate());
        map.put("customer", bill.getCustomer());
        map.put("factoryAddress", bill.getFactoryAddress());
        map.put("contacts", bill.getContacts());
        map.put("contactsPhone", bill.getContactsPhone());
        map.put("billOfLading", bill.getBillOfLading());
        map.put("boxType", bill.getBoxType());
        map.put("boxNum", bill.getBoxNum());
        map.put("driver", bill.getDriver());
        map.put("carNo", bill.getCarNo());
        map.put("driverPhone", bill.getDriverPhone());
        map.put("recordPeople", bill.getRecordPeople());
        map.put("getBoxLastDate", bill.getGetBoxLastDate());
        map.put("privatecode", bill.getPrivatecode());
        map.put("importDate", bill.getImportDate());
        map.put("sealno", bill.getSealno());
        map.put("vessel", bill.getVessel());
        map.put("serialno", bill.getSerialno());
        map.put("voyage", bill.getVoyage());
        map.put("weight", bill.getWeight());
        map.put("busiId", bill.getBusiId());
        map.put("getConPile", bill.getGetConPile());
        map.put("returnConPile", bill.getReturnConPile());
        map.put("orderNum", bill.getOrderNum()==null?null:bill.getOrderNum()+"");
        map.put("remark", bill.getRemark());
		map.put("createUser", loginUser.getId());
		map.put("jkxid", bill.getJkxid());
		getMapParameter(sql, map);
		count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean update(Bill bill, User loginUser) {
		int count = 0;
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sszg_bill set ");
        sql.append("relUserId=:relUserId,score=:score,qrcode=:qrcode ");
		sql.append(",packDate=:packDate,customer=:customer,factoryAddress=:factoryAddress ");
		sql.append(",contacts=:contacts,contactsPhone=:contactsPhone,billOfLading=:billOfLading ");
		sql.append(",boxType=:boxType,boxNum=:boxNum,driver=:driver ");
		sql.append(",carNo=:carNo,driverPhone=:driverPhone,recordPeople=:recordPeople ");
		sql.append(",getBoxLastDate=:getBoxLastDate,privatecode=:privatecode,importDate=:importDate ");
		sql.append(",sealno=:sealno,vessel=:vessel,serialno=:serialno ");
		sql.append(",voyage=:voyage,weight=:weight,orderNum=:orderNum ");
		sql.append(",getConPile=:getConPile,returnConPile=:returnConPile ");
		sql.append(",remark=:remark,updateTime=now(),updateUser=:updateUser where id=:id ");
        map.put("relUserId", bill.getRelUserId());
        map.put("score", bill.getScore());
        map.put("qrcode", bill.getQrcode());
        map.put("packDate", bill.getPackDate());
        map.put("customer", bill.getCustomer());
        map.put("factoryAddress", bill.getFactoryAddress());
        map.put("contacts", bill.getContacts());
        map.put("contactsPhone", bill.getContactsPhone());
        map.put("billOfLading", bill.getBillOfLading());
        map.put("boxType", bill.getBoxType());
        map.put("boxNum", bill.getBoxNum());
        map.put("driver", bill.getDriver());
        map.put("carNo", bill.getCarNo());
        map.put("driverPhone", bill.getDriverPhone());
        map.put("recordPeople", bill.getRecordPeople());
        map.put("getBoxLastDate", bill.getGetBoxLastDate());
        map.put("privatecode", bill.getPrivatecode());
        map.put("importDate", bill.getImportDate());
        map.put("sealno", bill.getSealno());
        map.put("vessel", bill.getVessel());
        map.put("serialno", bill.getSerialno());
        map.put("voyage", bill.getVoyage());
        map.put("weight", bill.getWeight());
		map.put("getConPile", bill.getGetConPile());
		map.put("returnConPile", bill.getReturnConPile());
        map.put("orderNum", bill.getOrderNum()==null?null:bill.getOrderNum()+"");
        map.put("remark", bill.getRemark());
		map.put("updateUser", loginUser.getId());
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean update2(Bill bill, User loginUser) {
		int count = 0;
		Map<String, String> map  = new HashMap<String, String>();
		StringBuffer sql =new StringBuffer("update sszg_bill set ");
        sql.append("relUserId=:relUserId,score=:score,qrcode=:qrcode ");
		sql.append(",packDate=:packDate,customer=:customer,factoryAddress=:factoryAddress ");
		sql.append(",contacts=:contacts,contactsPhone=:contactsPhone");
		sql.append(",boxNum=:boxNum,driver=:driver ");
		sql.append(",carNo=:carNo,driverPhone=:driverPhone,recordPeople=:recordPeople ");
		sql.append(",getBoxLastDate=:getBoxLastDate,privatecode=:privatecode,importDate=:importDate ");
		sql.append(",sealno=:sealno,vessel=:vessel,serialno=:serialno ");
		sql.append(",voyage=:voyage,weight=:weight,orderNum=:orderNum ");
		sql.append(",getConPile=:getConPile,returnConPile=:returnConPile ");
		sql.append(",remark=:remark,updateTime=now(),updateUser=:updateUser where id=:id ");
        map.put("relUserId", bill.getRelUserId());
        map.put("score", bill.getScore());
        map.put("qrcode", bill.getQrcode());
        map.put("packDate", bill.getPackDate());
        map.put("customer", bill.getCustomer());
        map.put("factoryAddress", bill.getFactoryAddress());
        map.put("contacts", bill.getContacts());
        map.put("contactsPhone", bill.getContactsPhone());
        map.put("boxNum", bill.getBoxNum());
        map.put("driver", bill.getDriver());
        map.put("carNo", bill.getCarNo());
        map.put("driverPhone", bill.getDriverPhone());
        map.put("recordPeople", bill.getRecordPeople());
        map.put("getBoxLastDate", bill.getGetBoxLastDate());
        map.put("privatecode", bill.getPrivatecode());
        map.put("importDate", bill.getImportDate());
        map.put("sealno", bill.getSealno());
        map.put("vessel", bill.getVessel());
        map.put("serialno", bill.getSerialno());
        map.put("voyage", bill.getVoyage());
        map.put("weight", bill.getWeight());
		map.put("getConPile", bill.getGetConPile());
		map.put("returnConPile", bill.getReturnConPile());
        map.put("orderNum", bill.getOrderNum()==null?null:bill.getOrderNum()+"");
        map.put("remark", bill.getRemark());
		map.put("updateUser", loginUser.getId());
		map.put("id", bill.getId());
		getMapParameter(sql, map);
		count = jdbcTemplate.update(sql.toString(), map);
		return count > 0;
	}

	@Override
	public boolean delete(Bill bill, User loginUser) {
		int count = 0;
		Map<String, String> map = null;
		if(bill.getIds().length()>0){
			for(String ID : bill.getIds().split(",")){
				map  = new HashMap<String, String>();
				if(ID==null || ID.equals("")){ break; }
				StringBuffer sql =new StringBuffer("update sszg_bill set del=1" +
						",updateUser=:updateUser, updateTime=now() "
						+ " where id =:id ");
				map.put("updateUser", loginUser.getId());
				map.put("id", ID);
				getMapParameter(sql, map);
				count = jdbcTemplate.update(sql.toString(),map);
			}
		}
		return count > 0;
	}
}
